package com.supplychain.financial.parent.common.util;

import com.supplychain.financial.parent.common.exception.CustomException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导出表格方法
 * @author lxb
 *
 */
public class ExcelUtil {

	public static HSSFWorkbook createHSSFWorkbook(List<Map<String, Object>> list, String []keys, String[] columnNames) {
		
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
		//设置列宽，第一个参数为第几列，第二个为宽度
		for (int i = 0; i < keys.length; i++) {
			sheet.setColumnWidth(i, 38*150);
		}
		//创建第一行
		HSSFRow row = sheet.createRow(0);
		
		//创建单元格格式
		HSSFCellStyle style = wb.createCellStyle();
		HSSFCellStyle style2 = wb.createCellStyle();
		
		Font f = wb.createFont();//创建字体
		Font f2 = wb.createFont();
		f.setFontHeightInPoints((short) 11);
		f.setColor(IndexedColors.BLACK.getIndex());
		f.setBold(true);
		f2.setFontHeightInPoints((short) 10);
		f2.setColor(IndexedColors.BLACK.getIndex());
		
		//设置第一种单元格样式
		style.setFont(f);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setBorderBottom(BorderStyle.THIN);
		style.setAlignment(HorizontalAlignment.CENTER);
		//设置第二种单元格样式
		style2.setFont(f2);
		style2.setBorderLeft(BorderStyle.THIN);
		style2.setBorderRight(BorderStyle.THIN);
		style2.setBorderTop(BorderStyle.THIN);
		style2.setBorderBottom(BorderStyle.THIN);
		style2.setAlignment(HorizontalAlignment.CENTER);
		
		//设置列名
		for (int i = 0; i < columnNames.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellValue(columnNames[i]);
			cell.setCellStyle(style);
		}
		//设置每列的值
		for (int i = 1; i < list.size(); i++) {
			HSSFRow row1 = sheet.createRow(i);
			for (int j = 0; j < keys.length; j++) {
				HSSFCell cell =row1.createCell(j);
				cell.setCellValue(list.get(i).get(keys[j]) == null ? "":list.get(i).get(keys[j]).toString());
				cell.setCellStyle(style2);
			}
		}
		
		return wb;
	}

	public static HSSFWorkbook createHSSFWorkbook1(List<Map<String, String>> list, String []keys, String[] columnNames) {

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(list.get(0).get("sheetName"));
		//设置列宽，第一个参数为第几列，第二个为宽度
		for (int i = 0; i < keys.length; i++) {
			sheet.setColumnWidth(i, 38*150);
		}
		//创建第一行
		HSSFRow row = sheet.createRow(0);

		//创建单元格格式
		HSSFCellStyle style = wb.createCellStyle();
		HSSFCellStyle style2 = wb.createCellStyle();

		Font f = wb.createFont();//创建字体
		Font f2 = wb.createFont();
		f.setFontHeightInPoints((short) 11);
		f.setColor(IndexedColors.BLACK.getIndex());
		f.setBold(true);
		f2.setFontHeightInPoints((short) 10);
		f2.setColor(IndexedColors.BLACK.getIndex());

		//设置第一种单元格样式
		style.setFont(f);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setBorderBottom(BorderStyle.THIN);
		style.setAlignment(HorizontalAlignment.CENTER);
		//设置第二种单元格样式
		style2.setFont(f2);
		style2.setBorderLeft(BorderStyle.THIN);
		style2.setBorderRight(BorderStyle.THIN);
		style2.setBorderTop(BorderStyle.THIN);
		style2.setBorderBottom(BorderStyle.THIN);
		style2.setAlignment(HorizontalAlignment.CENTER);

		//设置列名
		for (int i = 0; i < columnNames.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellValue(columnNames[i]);
			cell.setCellStyle(style);
		}
		//设置每列的值
		for (int i = 1; i < list.size(); i++) {
			HSSFRow row1 = sheet.createRow(i);
			for (int j = 0; j < keys.length; j++) {
				HSSFCell cell =row1.createCell(j);
				cell.setCellValue(list.get(i).get(keys[j]) == null ? "" : list.get(i).get(keys[j]).toString());
				cell.setCellStyle(style2);
			}
		}

		return wb;
	}
	
	public static Font createFont(HSSFWorkbook wb,short fontHeight,short color,boolean flag){
		Font font = wb.createFont();
		font.setFontHeightInPoints(fontHeight);
		font.setColor(color);
		font.setBold(flag);
		return font;
	} 
	
	public static HSSFCellStyle createHSSFCellStyle(HSSFWorkbook wb,Font font,BorderStyle border,HorizontalAlignment pos,VerticalAlignment vpos){
		HSSFCellStyle style = wb.createCellStyle();
		style.setFont(font);
		style.setBorderLeft(border);
		style.setBorderBottom(border);
		style.setBorderRight(border);
		style.setBorderTop(border);
		style.setAlignment(pos);
		style.setVerticalAlignment(vpos);
		return style;
	} 
	
	
	public static HSSFWorkbook createSIHSSFWorkbook(List<Map<String, Object>> list, String []keys, String[] columnNames,String[] mergeColNames){
		Map<String, Object> map = list.remove(0);
		String sheetName = map.get("sheetName").toString();
		String serverTime = map.get("serverTime").toString();
		String authorTime = map.get("authorTime") == null ? "": map.get("authorTime").toString();
		String areaServiceName = map.get("areaServiceName") == null ? "": map.get("areaServiceName").toString();
		String footer = map.get("footer") == null ? "": map.get("footer").toString();
		int rowNum = 0;
		HSSFWorkbook wb = new HSSFWorkbook();
		
		//创建单元格格式
		HSSFCellStyle style0 = createHSSFCellStyle(wb,createFont(wb,(short)17,IndexedColors.BLACK.getIndex(),true),BorderStyle.THIN,HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
		HSSFCellStyle style1 = createHSSFCellStyle(wb,createFont(wb,(short)14,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.LEFT,VerticalAlignment.CENTER);
		HSSFCellStyle style = createHSSFCellStyle(wb,createFont(wb,(short)13,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.LEFT,VerticalAlignment.CENTER);
		HSSFCellStyle style2 = createHSSFCellStyle(wb,createFont(wb,(short)12,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
		HSSFCellStyle style3 = createHSSFCellStyle(wb,createFont(wb,(short)13,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
		HSSFCellStyle style4 = createHSSFCellStyle(wb,createFont(wb,(short)12,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
		HSSFCellStyle style5 = createHSSFCellStyle(wb,createFont(wb,(short)13,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.RIGHT,VerticalAlignment.CENTER);
		
		//增加对空数据的处理，防止excel提示“数据可能丢失”
		if(list.isEmpty()) {
			HSSFSheet sheet = wb.createSheet(sheetName + "-没有数据");
			//设置列宽，第一个参数为第几列，第二个为宽度
			for (int i = 0; i < keys.length; i++) {
				sheet.setColumnWidth(i, 38*120);
			}
			//创建第一行
			HSSFRow row = sheet.createRow(rowNum ++);
			//设置列名
			for (int i = 0; i < columnNames.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(columnNames[i]);
				cell.setCellStyle(style);
			}
			return wb;
		}
		
		
		int sheetLength = 10000;
		int time = 0;
		while (true) {
			time ++;
			int beginIndex = (time - 1) * sheetLength;
			int endIndex = beginIndex + sheetLength;
			if (beginIndex > list.size() - 1) {
				break;
			}
			if (endIndex >list.size()) {
				endIndex = list.size();
			}

			List<Map<String, Object>> pageList = list.subList(beginIndex, endIndex);

			HSSFSheet sheet = wb.createSheet(sheetName +"-"+ time);

			//设置列宽，第一个参数为第几列，第二个为宽度
			for (int i = 0; i < keys.length; i++) {
				sheet.setColumnWidth(i, 38*120);
			}

			if(!"".equals(areaServiceName)){
				sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,columnNames.length-1));
				HSSFRow row3 = sheet.createRow(rowNum++);
				HSSFCell cell1 = row3.createCell(0);
				cell1.setCellValue(areaServiceName);
				cell1.setCellStyle(style3);
			}
			if(!"".equals(sheetName)){
				sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,columnNames.length-1));
				HSSFRow row0 = sheet.createRow(rowNum++);
				HSSFCell cell0 = row0.createCell(0);
				cell0.setCellValue(sheetName);
				cell0.setCellStyle(style0);
			}
			if(!"".equals(serverTime)){
				sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,columnNames.length-1));
				HSSFRow row4 = sheet.createRow(rowNum++);
				HSSFCell cell4 = row4.createCell(0);
				cell4.setCellValue(serverTime);
				cell4.setCellStyle(style4);
			}
			
			if(!"".equals(authorTime)){
				sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,columnNames.length-1));
				HSSFRow row1 = sheet.createRow(rowNum++);
				HSSFCell cell1 = row1.createCell(0);
				cell1.setCellValue(authorTime);
				cell1.setCellStyle(style1);
			}
			//创建第一行
			HSSFRow row = sheet.createRow(rowNum++);

			//设置列名
			for (int i = 0; i < columnNames.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(columnNames[i]);
				cell.setCellStyle(style);
			}
			HashMap<String,StoreLastDiffRow> mergeCv = new HashMap<String,StoreLastDiffRow>();
			for(int i = 0;i < mergeColNames.length;i++){
				mergeCv.put(mergeColNames[i], new StoreLastDiffRow(mergeColNames[i],mergeColNames[i],rowNum));
			}
			//设置每列的值
			for (int i = 0,pageListLen = pageList.size(); i < pageListLen; i++) {
				HSSFRow row2 = sheet.createRow(rowNum);
				Map<String, Object> param = pageList.get(i);
				for (int j = 0; j < keys.length; j++) {
					HSSFCell cell =row2.createCell(j);
					String key = keys[j].replace(" ", "");
					String value = param.get(key) == null ? "" : param.get(key).toString();
					cell.setCellValue(value);
					cell.setCellStyle(style2);
					if(mergeCv.containsKey(key)){
						StoreLastDiffRow sfr = mergeCv.get(key);
						if(!value.equals(sfr.getValue()) || (i == (pageListLen-1))){
							if((rowNum-1) > sfr.getIdx())
								sheet.addMergedRegion(new CellRangeAddress(sfr.getIdx(),rowNum-1,j,j));
							sfr.setIdx(rowNum);
							sfr.setValue(value);
						}
					}
					
				}
				rowNum ++;
			}
			if(!"".equals(footer)){
				sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,0,columnNames.length-1));
				HSSFRow row1 = sheet.createRow(rowNum++);
				HSSFCell cell1 = row1.createCell(0);
				cell1.setCellValue(footer);
				cell1.setCellStyle(style5);
			}
		}



		return wb;
	}
	/**
	 * 将数据生成为excel，默认一万条一个sheet
	 * @param list 要生成的数据，list[0] 为sheet的名称
	 * @param keys 列名对应在map里的key
	 * @param columnNames 列名
	 * @return 生成的excel文件
	 */
	public static HSSFWorkbook createHSSFWorkbook2(List<Map<String, Object>> list, String []keys, String[] columnNames) {
		Map<String, Object> map = list.remove(0);
		String sheetName = map.get("sheetName").toString();
		String serverTime = map.get("serverTime").toString();
		String authorTime = map.get("authorTime") == null ? "": map.get("authorTime").toString();
		String areaServiceName = map.get("areaServiceName") == null ? "": map.get("areaServiceName").toString();
		String footer = map.get("footer") == null ? "": map.get("footer").toString();
		int rowNum = 0;
		HSSFWorkbook wb = new HSSFWorkbook();

		//创建单元格格式
		HSSFCellStyle style0 = createHSSFCellStyle(wb,createFont(wb,(short)17,IndexedColors.BLACK.getIndex(),true),BorderStyle.THIN,HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
		HSSFCellStyle style1 = createHSSFCellStyle(wb,createFont(wb,(short)14,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.LEFT,VerticalAlignment.CENTER);
		HSSFCellStyle style = createHSSFCellStyle(wb,createFont(wb,(short)13,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.LEFT,VerticalAlignment.CENTER);
		HSSFCellStyle style2 = createHSSFCellStyle(wb,createFont(wb,(short)12,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.LEFT,VerticalAlignment.CENTER);
		HSSFCellStyle style3 = createHSSFCellStyle(wb,createFont(wb,(short)13,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
		HSSFCellStyle style4 = createHSSFCellStyle(wb,createFont(wb,(short)12,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
		HSSFCellStyle style5 = createHSSFCellStyle(wb,createFont(wb,(short)13,IndexedColors.BLACK.getIndex(),false),BorderStyle.THIN,HorizontalAlignment.RIGHT,VerticalAlignment.CENTER);
		//增加对空数据的处理，防止excel提示“数据可能丢失”
		if(list.isEmpty()) {
			HSSFSheet sheet = wb.createSheet(sheetName + "-没有数据");
			//设置列宽，第一个参数为第几列，第二个为宽度
			for (int i = 0; i < keys.length; i++) {
				sheet.setColumnWidth(i, 38*120);
			}
			//创建第一行
			HSSFRow row = sheet.createRow(rowNum ++);
			//设置列名
			for (int i = 0; i < columnNames.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(columnNames[i]);
				cell.setCellStyle(style);
			}
			return wb;
		}

		int sheetLength = 10000;
		int time = 0;
		while (true) {
			time ++;
			int beginIndex = (time - 1) * sheetLength;
			int endIndex = beginIndex + sheetLength;
			if (beginIndex > list.size() - 1) {
				break;
			}
			if (endIndex >list.size()) {
				endIndex = list.size();
			}

			List<Map<String, Object>> pageList = list.subList(beginIndex, endIndex);

			HSSFSheet sheet = wb.createSheet(sheetName +"-"+ time);

			//设置列宽，第一个参数为第几列，第二个为宽度
			for (int i = 0; i < keys.length; i++) {
				sheet.setColumnWidth(i, 38*120);
			}

			if(!"".equals(areaServiceName)){
				sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,columnNames.length-1));
				HSSFRow row3 = sheet.createRow(rowNum++);
				HSSFCell cell1 = row3.createCell(0);
				cell1.setCellValue(areaServiceName);
				cell1.setCellStyle(style3);
			}
			if(!"".equals(sheetName)){
				sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,columnNames.length-1));
				HSSFRow row0 = sheet.createRow(rowNum++);
				HSSFCell cell0 = row0.createCell(0);
				cell0.setCellValue(sheetName);
				cell0.setCellStyle(style0);
			}
			if(!"".equals(serverTime)){
				sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,columnNames.length-1));
				HSSFRow row4 = sheet.createRow(rowNum++);
				HSSFCell cell4 = row4.createCell(0);
				cell4.setCellValue(serverTime);
				cell4.setCellStyle(style4);
			}
			
			if(!"".equals(authorTime)){
				sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,columnNames.length-1));
				HSSFRow row1 = sheet.createRow(rowNum++);
				HSSFCell cell1 = row1.createCell(0);
				cell1.setCellValue(authorTime);
				cell1.setCellStyle(style1);
			}
			//创建第一行
			HSSFRow row = sheet.createRow(rowNum++);

			//设置列名
			for (int i = 0; i < columnNames.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(columnNames[i]);
				cell.setCellStyle(style);
			}

			//设置每列的值
			for (int i = 0; i < pageList.size(); i++) {
				HSSFRow row2 = sheet.createRow(rowNum++);
				Map<String, Object> param = pageList.get(i);
				for (int j = 0; j < keys.length; j++) {
					HSSFCell cell =row2.createCell(j);
					String key = keys[j].replace(" ", "");
					String value = param.get(key) == null ? "" : param.get(key).toString();
					cell.setCellValue(value);
					cell.setCellStyle(style2);
				}
			}
			if(!"".equals(footer)){
				sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,0,columnNames.length-1));
				HSSFRow row1 = sheet.createRow(rowNum++);
				HSSFCell cell1 = row1.createCell(0);
				cell1.setCellValue(footer);
				cell1.setCellStyle(style5);
			}
		}



		return wb;
	}

	public static  HSSFWorkbook getHSSFWorkbook(String filePath) throws CustomException {
		File file = new File(filePath);
		if(!file.isFile() || !file.exists()) {
			throw new CustomException("文件不存在或者不是文件格式");
		}
		FileInputStream fileInputStream = null;
		try {
			fileInputStream = new FileInputStream(file);
		} catch (FileNotFoundException e) {
			throw new CustomException("文件没有找到");
		}
		//Get the workbook instance for XLS file
		HSSFWorkbook hssfWorkbook = null;
		try {
			hssfWorkbook = new HSSFWorkbook(fileInputStream);
		} catch (IOException e) {
			e.printStackTrace();
			throw new CustomException("读取Excel失败");
		}
		try {
			fileInputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return hssfWorkbook;
	}

	
	public static HSSFWorkbook createHSSFWorkbook3(List<Map<String, Object>> list) {
		Map<String, Object> map = list.remove(0);
		String sheetName = map.get("sheetName").toString();
		String authorTime = map.get("authorTime") == null ? "": map.get("authorTime").toString();
		HSSFWorkbook wb = new HSSFWorkbook();

		//创建单元格格式
		HSSFCellStyle style0 = wb.createCellStyle();
		HSSFCellStyle style1 = wb.createCellStyle();
		HSSFCellStyle style = wb.createCellStyle();
		HSSFCellStyle style2 = wb.createCellStyle();
		
		Font f0 = wb.createFont();//创建字体
		Font f1 = wb.createFont();
		Font f = wb.createFont();
		Font f2 = wb.createFont();
		
		f0.setFontHeightInPoints((short) 15);
		f0.setColor(IndexedColors.BLACK.getIndex());
		f0.setBold(true);
		f1.setFontHeightInPoints((short) 12);
		f1.setColor(IndexedColors.BLACK.getIndex());
		f1.setBold(false);
		
		f.setFontHeightInPoints((short) 11);
		f.setColor(IndexedColors.BLACK.getIndex());
		f.setBold(true);
		
		f2.setFontHeightInPoints((short) 10);
		f2.setColor(IndexedColors.BLACK.getIndex());

		//设置第一种单元格样式
		style0.setFont(f0);
		style0.setBorderLeft(BorderStyle.THIN);
		style0.setBorderRight(BorderStyle.THIN);
		style0.setBorderTop(BorderStyle.THIN);
		style0.setBorderBottom(BorderStyle.THIN);
		style0.setAlignment(HorizontalAlignment.CENTER);
		
		style1.setFont(f1);
		style1.setBorderLeft(BorderStyle.THIN);
		style1.setBorderRight(BorderStyle.THIN);
		style1.setBorderTop(BorderStyle.THIN);
		style1.setBorderBottom(BorderStyle.THIN);
		style1.setAlignment(HorizontalAlignment.LEFT);
		
		style.setFont(f);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setBorderBottom(BorderStyle.THIN);
		style.setAlignment(HorizontalAlignment.CENTER);
		//设置第二种单元格样式
		style2.setFont(f2);
		style2.setBorderLeft(BorderStyle.THIN);
		style2.setBorderRight(BorderStyle.THIN);
		style2.setBorderTop(BorderStyle.THIN);
		style2.setBorderBottom(BorderStyle.THIN);
		style2.setAlignment(HorizontalAlignment.CENTER);

		//增加对空数据的处理，防止excel提示“数据可能丢失”
		if(list.isEmpty()) {
			HSSFSheet sheet = wb.createSheet(sheetName + "-没有数据");
	
			return wb;
		}
		Object[] keys = list.get(0).keySet().toArray();
		List<Object> colList = new ArrayList<Object>();
		Map<String, Object> colMap = list.get(0);
		for (String key :colMap.keySet()) {
			colList.add(colMap.get(key));
		}
		Object[] columnNames = colList.toArray();
		int sheetLength = 10000;
		int time = 0;
		while (true) {
			time ++;
			int beginIndex = (time - 1) * sheetLength+1;
			int endIndex = beginIndex + sheetLength;
			if (beginIndex > list.size() - 1) {
				break;
			}
			if (endIndex >list.size()) {
				endIndex = list.size();
			}
			
			List<Map<String, Object>> pageList = list.subList(beginIndex, endIndex);

			HSSFSheet sheet = wb.createSheet(sheetName.split(" ")[0] +"-"+ time);

			//设置列宽，第一个参数为第几列，第二个为宽度
			for (int i = 0; i < keys.length; i++) {
				sheet.setColumnWidth(i, 38*120);
			}
			sheet.addMergedRegion(new CellRangeAddress(0,0,0,columnNames.length-1));
			HSSFRow row0 = sheet.createRow(0);
			HSSFCell cell0 = row0.createCell(0);
			cell0.setCellValue(sheetName);
			cell0.setCellStyle(style0);
			
			sheet.addMergedRegion(new CellRangeAddress(1,1,0,columnNames.length-1));
			HSSFRow row1 = sheet.createRow(1);
			HSSFCell cell1 = row1.createCell(0);
			cell1.setCellValue(authorTime);
			cell1.setCellStyle(style1);
			//创建第一行
			HSSFRow row = sheet.createRow(2);

			//设置列名
			for (int i = 0; i < columnNames.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(columnNames[i].toString());
				cell.setCellStyle(style);
			}

			//设置每列的值
			for (int i = 0; i < pageList.size(); i++) {
				HSSFRow row2 = sheet.createRow(i+3);
				for (int j = 0; j < keys.length; j++) {
					HSSFCell cell =row2.createCell(j);
					cell.setCellValue(pageList.get(i).get(keys[j]) == null ? "" : pageList.get(i).get(keys[j]).toString());
					cell.setCellStyle(style2);
				}
			}
		}



		return wb;
	}
	/**
	 * 适用于第一行是标题行的excel，例如
	 * 姓名   年龄  性别  身高
	 * 张三   25  男   175
	 * 李四   22  女   160
	 * 每一行构成一个map，key值是列标题，value是列值。没有值的单元格其value值为null
	 * 返回结果最外层的list对应一个excel文件，第二层的list对应一个sheet页，第三层的map对应sheet页中的一行
	 * @throws Exception
	 */
	public static List<List<Map<String, String>>> readExcelWithTitle(String filePath) throws Exception{
		String fileType = filePath.substring(filePath.lastIndexOf(".") + 1, filePath.length());
		InputStream is = null;
		Workbook wb = null;
		try {
			is = new FileInputStream(filePath);

			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(is);
			} else if (fileType.equals("xlsx")) {
				//wb = new XSSFWorkbook(is);
				throw new Exception("文件格式不对");
			} else {
				throw new Exception("读取的不是excel文件");
			}

			List<List<Map<String, String>>> result = new ArrayList<List<Map<String,String>>>();//对应excel文件

			int sheetSize = wb.getNumberOfSheets();
			for (int i = 0; i < sheetSize; i++) {//遍历sheet页
				Sheet sheet = wb.getSheetAt(i);
				List<Map<String, String>> sheetList = new ArrayList<Map<String, String>>();//对应sheet页

				List<String> titles = new ArrayList<String>();//放置所有的标题

				int rowSize = sheet.getLastRowNum() + 1;
				for (int j = 0; j < rowSize; j++) {//遍历行
					Row row = sheet.getRow(j);
					if (row == null) {//略过空行
						continue;
					}
					int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列
					if (j == 0) {//第一行是标题行
						for (int k = 0; k < cellSize; k++) {
							Cell cell = row.getCell(k);
							titles.add(cell.toString());
						}
					} else {//其他行是数据行
						Map<String, String> rowMap = new HashMap<String, String>();//对应一个数据行
						for (int k = 0; k < titles.size(); k++) {
							Cell cell = row.getCell(k);
							String key = titles.get(k);
							String value = null;
							if (cell != null) {
								value = cell.toString();
							}
							rowMap.put(key, value);
						}
						sheetList.add(rowMap);
					}
				}
				result.add(sheetList);
			}

			return result;
		} catch (FileNotFoundException e) {
			throw e;
		} finally {
			if (wb != null) {
				wb.close();
			}
			if (is != null) {
				is.close();
			}
		}
	}

	public static void main(String[] args) throws Exception {
		List<List<Map<String, String>>> result = readExcelWithTitle("/Users/zhengchuan/Desktop/零售户信息收集.xls");
		List<Map<String, String>> seet1 = result.get(0);
		System.out.println(seet1);
		System.out.println(transformParams(seet1));
		//System.out.println(state);
	}

	public static  List<Map<String, String>> transformParams(List<Map<String, String>> params) {
		List<Map<String, String>> results = new ArrayList<>();
		for (Map<String, String> param :  params) {
			if (param.get("客户编码") == null || param.get("客户编码").equals("")) {
				continue;
			}
			Map<String, String> result = new HashMap<>(13,1.0F);
			result.put("tradeSubType","'DK'");
			result.put("merId","");
			result.put("userId","2410");
			result.put("customerId",param.get("客户编码"));
			result.put("accNo",param.get("支付银行卡号"));
			result.put("name",param.get("持卡人姓名"));
			result.put("certCode",param.get("持卡人身份证"));
			result.put("nbr",param.get("银行卡预留手机号"));
			result.put("cvn2",param.get("信用卡CNV码(信用卡必填)") == null ? "" : param.get("信用卡CNV码(信用卡必填)"));
			String expireDate = param.get("银行号有效期月/年(信用卡必填)");
			System.out.println(expireDate);
			if (expireDate != null && !expireDate.equals("") && expireDate.length() == 4) {
				System.out.println("来到有效期处理处。。。");
				String pr = expireDate.substring(2, 4);
				String po = expireDate.substring(0, 2);
				expireDate = pr + po;
			}else {
				expireDate = "";
			}
			result.put("expireDate",expireDate);
			result.put("cardType",param.get("卡类型(信用卡:01,储蓄卡:02)"));
			result.put("smsCode","123456");
			result.put("sign","xxx");
			results.add(result);
		}

		return results;
	}
	private static Object[] formatColumnLabel(Object[] keys) {
		for(int i=0;i<keys.length;i++){
		    StringBuffer sb = new StringBuffer();
		    boolean line = false;
		    for (char ch : keys[i].toString().toLowerCase().toCharArray())
		      if (ch == '_') {
		        line = true;
		      }
		      else if (line) {
		        line = false;
		        sb.append((char)((ch >= 'a') && (ch <= 'z') ? ch - 'a' + 65 : ch));
		      }
		      else
		      {
		        sb.append(ch);
		      }
		    keys[i]=sb.toString();
		}
	    return keys;
	  }
}

class StoreLastDiffRow{
	private String key;
	private String value;
	private int idx;
	
	public StoreLastDiffRow(String key,String value,int idx){
		this.key = key;
		this.value = value;
		this.idx = idx;
	}
	public String getKey() {
		return key;
	}
	public void setKey(String key) {
		this.key = key;
	}
	public String getValue() {
		return value;
	}
	public void setValue(String value) {
		this.value = value;
	}
	public int getIdx() {
		return idx;
	}
	public void setIdx(int idx) {
		this.idx = idx;
	}
	
	
}
